﻿using System;
using Microsoft.Extensions.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Collections;

namespace ConnectionSamples
{
    class Program
    {
        static void Main(string[] args)
        {
            if (args.Length != 1)
            {
                ShowUsage();
                return;
            }
            switch (args[0])
            {
                case "-o":
                    OpenConnection();
                    break;
                case "-c":
                    ConnectionUsingConfig();
                    break;
                case "-i":
                    ConnectionInformation();
                    break;
                case "-t":
                    Transactions();
                    break;
                default:
                    ShowUsage();
                    break;
            }
            Console.WriteLine("completed");

            Console.ReadLine();
        }

        public static void ShowUsage()
        {
            Console.WriteLine("ConnectionSamples command");
            Console.WriteLine("\t-o\tOpen Connection");
            Console.WriteLine("\t-c\tUse Configuration File");
            Console.WriteLine("\t-i\tConnection Information");
            Console.WriteLine("\t-t\tTransactions");
        }

        /// <summary>
        /// 打开与关闭数据库连接
        /// </summary>
        public static void OpenConnection()
        {
            string connectionString = @"server=(localdb)\MSSQLLocalDB;" +
                            //连接到的服务器，使用关键数字server=
                            //或者使用Data Source=
                            "integrated security=SSPI;" +
                            //integrated security使用Windws授权                            
                            "database=Books";
            //连接到的数据库实例
            //https://www.connectionstrings.com/  上可找到各种连接串  

            var connection = new SqlConnection(connectionString);
            connection.Open();
            
            // Do something useful
            Console.WriteLine("connection opened");
            connection.Close();
        }

        /// <summary>
        /// 从配置文件中读取连接字符串
        /// </summary>
        public static void ConnectionUsingConfig()
        {
            //ConfigurationBuilder用于生成基于键/值的配置设置，以供在应用程序中使用。
            var configurationBuilder = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory()) //ConfigurationBuilder的扩展方法，设置当前目录为配置文件基目录
                .AddJsonFile("config.json");//添加config.json文件作为配置文件
            IConfiguration config = configurationBuilder.Build();//创建配置变量
          
            //按配置文件的json格式层次获得连接字符串
            string connectionString = config["Data:DefaultConnection:ConnectionString"];

            //拿到连接字符串后，即可如OpenConnection方法那样打开关闭数据库连接。
            //本例仅作输出
            Console.WriteLine(connectionString);
        }

        /// <summary>
        /// 从配置文件中获取连接字符串，与上述方法相同，区别在于返回值
        /// </summary>
        /// <returns>返回连接字符串</returns>

        public static string GetConnectionString()
        {
            var configurationBuilder = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("config.json");

            IConfiguration config = configurationBuilder.Build();
            string connectionString = config["Data:DefaultConnection:ConnectionString"];
            return connectionString;
        }

        public static void ConnectionInformation()
        {
            //打开和关闭连接
            using (var connection = new SqlConnection(GetConnectionString()))
            {
                //SqlConnection定义了InfoMessage和StateChange事件
                //每次从SqlServer返回一个信息或警告消息时，就触发InfoMessage事件。
                //此处是注册事件处理方法时直接使用lambda表达式
                connection.InfoMessage += (sender, e) =>
                {
                    Console.WriteLine($"警告/信息: {e.Message}");
                };

                //连接状态变化时，就触发StateChange事件。
                connection.StateChange += (sender, e) =>
                {
                    Console.WriteLine($"当前状态: {e.CurrentState}, 之前的状态是: {e.OriginalState}");
                };

                try
                {
                    //设置该项为真就可以调用统计检索信息connection.RetrieveStatistics
                    connection.StatisticsEnabled = true;

                    //先前被视为异常的错误现在将作为 InfoMessage 事件进行处理。
                    //所有事件都立即触发，并由事件处理程序进行处理。
                    connection.FireInfoMessageEventOnUserErrors = true;
                   
                    connection.Open();

                    Console.WriteLine("connection opened");

                    // Do something useful
                    //创建SQL命令对象
                    SqlCommand command = connection.CreateCommand();

                    //创建SQL命令语句 查询Titl和Publisher ,此处故意设置错误，引发连接信息事件。
                    command.CommandText = "SELECT Titl, Publisher FROM [ProCSharp].[Books]";

                    //将 CommandText 发送到 Connection，并生成 SqlDataReader
                    SqlDataReader reader = command.ExecuteReader();
                    //
                    while (reader.Read())
                    {
                        Console.WriteLine($"{reader.GetString(0)} {reader.GetString(1)}");
                    }
                    //
                    IDictionary statistics = connection.RetrieveStatistics();
                    //
                    ShowStatistics(statistics);
                    //
                    connection.ResetStatistics();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }

        private static void ShowStatistics(IDictionary statistics)
        {
            Console.WriteLine("统计");
            foreach (var key in statistics.Keys)
            {
                Console.WriteLine($"{key}, 值: {statistics[key]}");
            }
            Console.WriteLine();
        }

        public static void Transactions()
        {
            string connectionString = GetConnectionString();
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlTransaction tx = connection.BeginTransaction();
                tx.Save("one");
                tx.Commit();
            }
        }
    }
}
